﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SMALLERP.DataClass;
using System.Data.SqlClient;
using System.IO;
using SMALLERP;
using XJLSC.PM;

namespace XJLSC.FM
{
    public partial class CGJHC : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;
        public CGJHC()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strSql = null;
            strSql = "SELECT A.LA001 品号,MB002 品名,MB003 规格,CEILING((SELECT CEILING(ISNULL(SUM(DDSL),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS DDSL,CEILING((SELECT CEILING(ISNULL(SUM(DDJE),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS DDJE,CEILING((SELECT CEILING(ISNULL(SUM(JHSL+CKSL+TZSL),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112))) AS QCKC,";
            //strSql =strSql+"(SELECT ISNULL(SUM(JHJE+CKJE+TZJE),0) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112)) AS QCJE,";
            strSql =strSql+"CEILING((SELECT CEILING(ISNULL(SUM(JHSL),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS JHSL,";
            //strSql =strSql+"(SELECT ISNULL(SUM(JHJE),0) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) AS JHJE,";
            strSql =strSql+"CEILING((SELECT CEILING(ISNULL(SUM(CKSL),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS CKSL,";
            strSql =strSql+"(SELECT ISNULL(SUM(CKJE),0) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) AS CKJE,";
            strSql =strSql+"CEILING((SELECT CEILING(ISNULL(SUM(TZSL),0)) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS TZSL, ";
            //strSql =strSql+"CEILING((SELECT ISNULL(SUM(TZJE),0) FROM V_CGJHC B WHERE B.LA001=A.LA001 AND B.LA004>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND B.LA004<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112))) AS TZJE";
            strSql = strSql + "0 AS JCSL FROM V_CGJHC A LEFT JOIN INVMB ON A.LA001=MB001 LEFT JOIN INVMA ON MB005=MA002 AND MA001='1' WHERE  (A.LA001 LIKE '" + textBox13.Text + "%' OR '" + textBox13.Text + "'='') AND (MB002 LIKE '%" + textBox11.Text + "%' OR '" + textBox11.Text + "'='')";
            strSql =strSql+" GROUP BY A.LA001,MB002,MB003,MA003 ORDER BY MB002";
            try
            {
               
                DataTable dt;
                dataGridView1.Columns.Clear();
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                dataGridView1.DataSource = dt;

                this.dataGridView1.Columns["DDSL"].HeaderText = "订单数量";
                this.dataGridView1.Columns["DDJE"].HeaderText = "订单金额";
                this.dataGridView1.Columns["QCKC"].HeaderText = "期初库存";
               // this.dataGridView1.Columns["QCJE"].HeaderText = "期初金额";
                this.dataGridView1.Columns["JHSL"].HeaderText = "入库数量";
               // this.dataGridView1.Columns["JHJE"].HeaderText = "入库金额";
                this.dataGridView1.Columns["CKSL"].HeaderText = "出库数量";
                this.dataGridView1.Columns["CKJE"].HeaderText = "出库金额";
                this.dataGridView1.Columns["TZSL"].HeaderText = "调整数量";
                //this.dataGridView1.Columns["TZJE"].HeaderText = "调整金额";
                this.dataGridView1.Columns["JCSL"].HeaderText = "结存数量";

                //DataGridViewTextBoxColumn acCode1 = new DataGridViewTextBoxColumn();
                //acCode1.Name = "JCSL";
                //acCode1.DataPropertyName = "JCSL";
                //acCode1.HeaderText = "结存数量";
                //acCode1.DefaultCellStyle.NullValue = "0";
                //dataGridView1.Columns.Add(acCode1);

                //DataGridViewTextBoxColumn acCode2 = new DataGridViewTextBoxColumn();
                //acCode2.Name = "JCJE";
                //acCode2.DataPropertyName = "JCJE";
                //acCode2.HeaderText = "结存金额";
                //acCode1.DefaultCellStyle.NullValue = "0";
                //dataGridView1.Columns.Add(acCode2);

                this.dataGridView1.Columns["规格"].Width = 100;
                this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView1.AllowUserToAddRows = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
            double jcsl = 0.00;
            double jcje = 0.00;
            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {

                jcsl = Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["QCKC"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["JHSL"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["CKSL"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["TZSL"].Index].Value);
               // jcje = Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["QCJE"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["JHJE"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["CKJE"].Index].Value) + Convert.ToDouble(this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["TZJE"].Index].Value);
                this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["JCSL"].Index].Value = jcsl.ToString();
                //this.dataGridView1.Rows[i].Cells[this.dataGridView1.Columns["JCJE"].Index].Value = jcje.ToString();
            }
            double sum1 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum1 += double.Parse(this.dataGridView1.Rows[i].Cells["QCKC"].Value.ToString());
            }
            textBox1.Text = sum1.ToString();

            //double sum2 = 0;
            //for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            //{
            //    sum2 += double.Parse(this.dataGridView1.Rows[i].Cells["QCJE"].Value.ToString());
            //}
            //textBox2.Text = sum2.ToString();

            double sum3 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum3 += double.Parse(this.dataGridView1.Rows[i].Cells["JHSL"].Value.ToString());
            }
            textBox3.Text = sum3.ToString();

            //double sum4 = 0;
            //for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            //{
            //    sum4 += double.Parse(this.dataGridView1.Rows[i].Cells["JHJE"].Value.ToString());
            //}
            //textBox4.Text = sum4.ToString();

            double sum5 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum5 += double.Parse(this.dataGridView1.Rows[i].Cells["CKSL"].Value.ToString());
            }
            textBox5.Text = sum5.ToString();

            double sum6 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum6 += double.Parse(this.dataGridView1.Rows[i].Cells["CKJE"].Value.ToString());
            }
            textBox6.Text = sum6.ToString();

            //double sum7 = 0;
            //for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            //{
            //    sum7 += double.Parse(this.dataGridView1.Rows[i].Cells["TZJE"].Value.ToString());
            //}
            //textBox7.Text = sum7.ToString();

            double sum8 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum8 += double.Parse(this.dataGridView1.Rows[i].Cells["TZSL"].Value.ToString());
            }
            textBox8.Text = sum8.ToString();

            //double sum9 = 0;
            //for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            //{
            //    sum9 += double.Parse(this.dataGridView1.Rows[i].Cells["JCJE"].Value.ToString());
            //}
            //textBox9.Text = sum9.ToString();

            double sum10 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum10 += double.Parse(this.dataGridView1.Rows[i].Cells["JCSL"].Value.ToString());
            }
            textBox10.Text = sum10.ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {

            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (dataGridView1.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView1.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView1.Columns.Count; k++)
                        {
                            if (dataGridView1.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }

            }
        }

        private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            string x1 = "";
            string x2 = "";
            string x3 = "";
            string x4 = "";
            string x5 = "";
            string x6 = "";
            x1 = dataGridView1["品号", dataGridView1.CurrentCell.RowIndex].Value.ToString();
            x2 = dateTimePicker1.Value.ToShortDateString();
            x3 = dateTimePicker2.Value.ToShortDateString();
            x4 = "";
            x5 = "";


            if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["JHSL"].Index)
            {
                x6 = "入库数量";

            }
            else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["CKSL"].Index)
            {
                x6 = "出库数量";

            }
            else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["DDSL"].Index)
            {
                x6 = "订单数量";

            }     

            else
            {
                x6 = "其他";
                MessageBox.Show("本列无明细数据", "软件提示");
            }

            if (x6 != "其他")
            {
                HXMXCX xx = new HXMXCX(x1, x2, x3, x4, x5, x6);
                CHAXUN f = new CHAXUN();
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                f.Show(this);
            }
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }
    }
}
